package com.mp.approvalreq.dao.impl;

import com.mp.approvalreq.dao.ParticipantDao;
import com.mp.approvalreq.entity.Participant;
import com.mp.approvalreq.util.ListHelper;
import com.plusmoney.util.SQLLogger;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class ParticipantDaoImpl extends AbstractDao implements ParticipantDao {

    private static final Logger logger = LoggerFactory.getLogger(ParticipantDaoImpl.class);

    private Connection connection;

    public ParticipantDaoImpl(Connection connection) {
        this.connection = connection;
    }

    private PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    @Override
    String getQuerySql(String condition) {
        final String query = "select id, company_id, request_id, contact_id, company_info_id,"
                + " is_read"
                + " from sys_approval_participant";
        return String.format("%s %s", query, condition);
    }

    @Override
    Participant getObject(ResultSet rs) throws SQLException {
        Participant participant = new Participant();
        participant.setId(rs.getInt(1));
        participant.setCompanyId(rs.getInt(2));
        participant.setRequestId(rs.getInt(3));
        participant.setContactId(rs.getInt(4));
        participant.setCompanyInfoId(rs.getInt(5));

        participant.setIsRead(rs.getInt(6));
        return participant;
    }

    @Override
    public void insertBatchParticipants(List<Participant> participants) throws SQLException {
        String sql = "insert into sys_approval_participant"
                + " set company_id = ?, request_id = ?, contact_id = ?, company_info_id = ?, is_read = ?,"
                + " create_time = now()";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Participant participant : participants) {
                ps.setInt(1, participant.getCompanyId());
                ps.setInt(2, participant.getRequestId());
                ps.setInt(3, participant.getContactId());
                ps.setInt(4, participant.getCompanyInfoId());
                ps.setInt(5, participant.getIsRead());
                logger.info(SQLLogger.logSQL(sql, participant.getCompanyId(), participant.getRequestId(),
                        participant.getContactId(), participant.getCompanyInfoId(), participant.getIsRead()));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public void updateParticipantState(Integer requestId, Integer companyId) throws SQLException {
        String sql = "update sys_approval_participant set state = 2 where request_id = ? and company_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyId));

            ps.executeUpdate();
        }
    }

    @Override
    public List<Integer> selectParticipateRequestIdsByCompanyInfoId(Integer companyId, Integer companyInfoId, String finishedSequence,
                                                                    String financialStatusSequence, String orderBy) throws SQLException {
        List<Integer> list = new ArrayList<>();
        if (StringUtils.isBlank(finishedSequence)) {
            return list;
        }
        String sql = "select distinct(p.request_id)"
                + " from sys_approval_participant p"
                + " left join request_flow f on p.request_id = f.Frequest_id"
                + " where p.company_id = ? and p.company_info_id = ? and f.Ffinished in(" + finishedSequence + ")"
                + " and f.Fis_resubmit = 0 and f.Ffinished != -1";


        // 排序
        if (StringUtils.isEmpty(orderBy) || "0".equals(orderBy) || "1".equals(orderBy)) {
            sql += " order by f.Frequest_id desc";
        } else if ("2".equals(orderBy)) {
            sql += " order by f.Flatest_approved_time desc";
        } else if ("3".equals(orderBy)) {
            sql += " order by f.Ffinally_cc_time desc";
        }

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public Map<Integer, List<Participant>> selectBatchParticipant(String requestIds) throws SQLException {
        Map<Integer, List<Participant>> map = new HashMap<>();
        if (StringUtils.isBlank(requestIds)) {
            return map;
        }
        String sql = getQuerySql("where request_id in(" + requestIds + ")");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Participant participant = getObject(rs);
                    if (!map.containsKey(participant.getRequestId())) {
                        map.put(participant.getRequestId(), new ArrayList<>());
                    }
                    map.get(participant.getRequestId()).add(participant);
                }
            }
        }
        return map;
    }

    @Override
    public void deleteRedPoint(Integer requestId, Integer companyInfoId) throws SQLException {
        String sql = "update sys_approval_participant set is_read = 1 where request_id = ? and company_info_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyInfoId));

            ps.executeUpdate();
        }
    }

    @Override
    public void deleteRedPoint(Integer requestId) throws SQLException {
        String sql = "update sys_approval_participant set is_read = 1 where request_id = ?";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            ps.executeUpdate();
        }
    }

    @Override
    public List<Integer> selectUnReadRequestIds(Integer companyId, Integer companyInfoId) throws SQLException {
        long time = System.currentTimeMillis();
        List<Integer> list = new ArrayList<>();
        String sql = "select request_id from sys_approval_participant where company_id = ? and company_info_id = ? and is_read = 0 and state = 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        logger.info("耗时:{}", System.currentTimeMillis() - time);
        return list;
    }

    @Override
    public List<Participant> selectParticipateRedPointInfo(Integer requestId, Integer companyInfoId) throws SQLException {
        List<Participant> list = new ArrayList<>();
        String sql = getQuerySql("where request_id = ? and company_info_id = ? and is_read = 0");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, requestId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Participant> selectParticipateRedPointInfo(Integer requestId) throws SQLException {
        List<Participant> list = new ArrayList<>();
        String sql = getQuerySql("where request_id = ? and is_read = 0");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectUserNearlyParticipant(Integer companyInfoId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select p.contact_id"
                + " from sys_approval_participant p"
                + " LEFT JOIN request_flow f on p.request_id = f.Frequest_id"
                + " where f.Fapplicant_ciid = ?"
                + " order by p.id desc limit 200";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Participant> selectParticipantsByRequestId(Integer requestId) throws SQLException {
        List<Participant> list = new ArrayList<>();
        String sql = getQuerySql("where request_id = ?");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

}
